"""    read.py
jython script to read excel
"""   
import sys

sys.path.append('poi-3.10.1/poi-3.10.1-20140818.jar')
sys.path.append('poi-3.10.1/poi-ooxml-3.10.1-20140818.jar')
sys.path.append('poi-3.10.1/poi-ooxml-schemas-3.10.1-20140818.jar')
sys.path.append('poi-3.10.1/poi-scratchpad-3.10.1-20140818.jar')
sys.path.append('poi-3.10.1/poi-excelant-3.10.1-20140818.jar')
sys.path.append('poi-3.10.1/ooxml-lib/dom4j-1.6.1.jar')
sys.path.append('poi-3.10.1/ooxml-lib/stax-api.1.0.1.jar')
sys.path.append('poi-3.10.1/ooxml-lib/xmlbeans-2.6.0.jar')
sys.path.append('poi-3.10.1/lib/junit-4.11.jar')
sys.path.append('poi-3.10.1/lib/log4j-1.2.13.jar')
sys.path.append('poi-3.10.1/lib/commons-codec-1.5.jar')
sys.path.append('poi-3.10.1/lib/commons-logging-1.1.jar')



from org.apache.poi.ss.usermodel import *
from org.apache.poi.hssf.util import HSSFColor
from java.io import *



# Process Excel
# Read it row by row
def read_excel(excel_name,worksheetnr):
    fileName = excel_name
    print "Excel: %s" % fileName
    file = File(fileName)
    wb = WorkbookFactory.create(file)
    sheet = wb.getSheetAt(worksheetnr)

    # get No. of rows
    rows = sheet.getPhysicalNumberOfRows()
    print ""
    print "Excel File Information"
    print wb, sheet, rows
    print ""

    cols = 0 # No. of columns
    tmp = 0

    # This trick ensures that we get the data properly even if it
    # doesn't start from first few rows
    for i in range(0, 10,1):
        row = sheet.getRow(i)
        if(row != None):
            tmp = sheet.getRow(i).getPhysicalNumberOfCells()
            if tmp > cols:
                cols = tmp

    print "Number of physical columns: %i" % cols
    print ""

    print "Content"
    for r in range(0, rows, 1): # rows
        row = sheet.getRow(r)
        cellcontents = ""
        # print r
        if(row != None):
            for c in range(0, cols, 1): # col
                cell = row.getCell(c)
                if cell != None:
                    #print cell
                    cellcontents += "\t" + cell.toString()
                else:
                    cellcontents += "\t*NVL*"
        print r, cellcontents

# Process Excel
# Read it row by row
def process_excel(excel_name):
    fileName = excel_name
    print "Excel: %s" % fileName
    file = File(fileName)    
    wb = WorkbookFactory.create(file)
    sheet = wb.getSheetAt(0)
    
    # sheet to write to
    fileOut = FileOutputStream("OUT_"+excel_name)
    outsheet = wb.createSheet("cm")
    style0 = wb.createCellStyle()
    font0 = wb.createFont()
    font0.setColor(HSSFColor.RED.index)
    style0.setFont(font0)
    

    # get No. of rows
    rows = sheet.getPhysicalNumberOfRows()
    print ""
    print "Excel File Information"
    print wb, sheet, rows
    print ""

    cols = 0 # No. of columns
    tmp = 0

    # This trick ensures that we get the data properly even if it
    # doesn't start from first few rows
    for i in range(0, 10,1):
        row = sheet.getRow(i)
        if(row != None):
            tmp = sheet.getRow(i).getPhysicalNumberOfCells()
            if tmp > cols:
                cols = tmp

    print "Number of physical columns: %i" % cols
    print ""

    print "Content"
    questid = 1
    for r in range(0, rows, 1): # rows
        row = sheet.getRow(r)
        cellcontents = ""
        isquestion = 1
        # print r
        if(row != None):
            for c in range(0, cols, 1): # col
                cell = row.getCell(c)
                if cell != None: 
                    if c > 0 and isquestion == 1:
                        outrow = outsheet.createRow(questid)                        
                        # id
                        outcell = outrow.createCell(0)
                        outcell.setCellValue("FRAGE"+str(questid))
                        outcell.setCellStyle(style0)
                        # text
                        outcell = outrow.createCell(1)
                        outcell.setCellValue(cell.toString())
                        outcell.setCellStyle(style0)
                        questid += 1
                    #print cell                   
                    cellcontents += "\t" + cell.toString()
                else: 
                    cellcontents += "\t*NVL*"
                    isquestion = 0
        print r, cellcontents
    wb.write(fileOut)
    fileOut.close()
    

if __name__ == '__main__':
    process_excel("cf.xlsx")
    read_excel("OUT_cf.xlsx",1)
